--POUR CONTROLE

define erp='erp_mli2.';
define outils='outils_mli2.';

/*
CREATE TABLE se_art_prix AS (SELECT * FROM &erp.art_prix WHERE 1=2);

  PROMPT CREATE INDEX se_ART_PRIX_01
CREATE INDEX se_ART_PRIX_01
  ON se_ART_PRIX (
    CLASSE1_LIB,
    TYPPRIX_COD,
    DEB_DAT
  )
  NOLOGGING
;
  PROMPT CREATE INDEX &erp.ART_PRIX_01
CREATE INDEX &erp.ART_PRIX_01
  ON &erp.ART_PRIX (
    CLASSE1_LIB,
    TYPPRIX_COD,
    DEB_DAT
  )
  NOLOGGING
;
*/


truncate table se_ART_PRIX;


/* PMD sur les rgp � mettre sur les complet de reference */
 INSERT INTO se_ART_PRIX
 (SELECT DISTINCT 0 enreg_id,
         NULL gen_soc_cod,
         NULL ets_cod,
         NULL depot_cod,
         Nvl(COMBSTO_REF,0) combsto_id,
         c.four unfab_cod,
         null fournisseur_cod,
         'PRI' typprix_cod,
         NULL soc_cod,
         To_Date('31/12/1967','dd/mm/yyyy')+d.datap2 deb_dat,
         /*To_Date('31/12/1967','dd/mm/yyyy')+df.datfin*/null fin_dat,
         NULL prix_qt,
         Round(PXTR2 /100,4) prix_px,
         'M' maj_typ,
         'N' enreg_s,
         NULL prixdemande_typ,
         NULL rechpref_dat,
         NULL prixtrouve_typ,
         NULL majpref_dat,
         NULL ponderationpx_coef,
         'R' traitement_typ,
         c.art classe1_lib,
         NULL classe2_lib,
         NULL classe3_lib,
         NULL classe4_lib
    FROM UV_PCATALOGUES c,/*UV_PCATALOGUES_TRANCHE t,*/UV_PCATALOGUES_DATAP2 d/*,UV_PCATALOGUES_DATFIN df*/,UV_PCATALOGUES_PXTR2 p,temp_conv_RGP_combstoref conv
WHERE d.id=c.id
  AND d.id=p.id AND d.rangmulti2=p.rangmulti2
  AND conv.clergp=c.ART
  AND conv.IS_COMPREF='COMPREF'

     );
 /* SELECT * FROM  temp_conv_RGP_combstoref*/
 UPDATE se_ART_PRIX tf SET enreg_id=Nvl((select Max(enreg_id) FROM &erp.ART_PRIX f WHERE f.combsto_id=tf.combsto_id AND f.unfab_cod=tf.unfab_cod AND f.typprix_cod=tf.typprix_cod AND f.DEB_DAT=tf.DEB_DAT),0) ;
 UPDATE se_ART_PRIX tf SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id =0;
 --select Count(*) from se_ART_PRIX where enreg_id=0

/* suppression de doublons pour memes combsto et dates*/
delete se_art_prix p WHERE EXISTS (
SELECT 1 FROM (
 SELECT combsto_id,unfab_cod,deb_dat,typprix_cod,fin_dat/*,outils_mli2.stragg(prix_px)*/,Count(*)
 FROM se_art_prix
 GROUP BY combsto_id,unfab_cod,deb_dat,fin_dat,typprix_cod HAVING Count(*)>1 ) r
WHERE r.combsto_id=p.combsto_id
  AND r.deb_dat=p.deb_dat
  AND r.typprix_cod=p.typprix_cod
  AND r.unfab_cod=p.unfab_cod
   )  ;

 delete se_art_prix a
 WHERE NOT EXISTS (SELECT 1 FROM erp_mli2.vrt_combsto v WHERE v.combsto_id=a.combsto_id) ;

 DELETE se_art_prix a
 WHERE NOT EXISTS (SELECT 1 FROM erp_mli2.par_unfab v WHERE v.unfab_cod=a.unfab_cod) ;

 DELETE FROM &erp.art_prix p WHERE typprix_cod='PRI' AND NOT EXISTS (SELECT 1 FROM se_art_prix pp WHERE pp.enreg_id=p.enreg_id) ;


 CALL uvse_synchroverserp('enreg_id','ART_PRIX','N') ;

 commit;

select * from (
SELECT &outils.stragg(source) source, enreg_id, gen_soc_cod, ets_cod, depot_cod, combsto_id, unfab_cod, fournisseur_cod, typprix_cod, soc_cod, deb_dat, fin_dat, prix_qt, prix_px, maj_typ, enreg_s, prixdemande_typ, rechpref_dat, prixtrouve_typ, majpref_dat, ponderationpx_coef, traitement_typ, classe1_lib, classe2_lib, classe3_lib, classe4_lib
FROM (
  SELECT 'uv' source ,tf.* FROM se_ART_PRIX tf where typprix_cod in ('PRI')
  UNION ALL
  SELECT 'se' source ,f.* FROM &erp.ART_PRIX f where typprix_cod in ('PRI')
  )

GROUP BY enreg_id, gen_soc_cod, ets_cod, depot_cod, combsto_id, unfab_cod, fournisseur_cod, typprix_cod, soc_cod, deb_dat, fin_dat, prix_qt, prix_px, maj_typ, enreg_s, prixdemande_typ, rechpref_dat, prixtrouve_typ, majpref_dat, ponderationpx_coef, traitement_typ, classe1_lib, classe2_lib, classe3_lib, classe4_lib
HAVING Count(1)!=2 ) r

ORDER BY CLASSE1_LIB,typprix_cod,deb_dat,combsto_id ;

